package com.neuedu.Dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



import com.neuedu.projos.Product;
import com.neuedu.utils.CloseUtils;
import com.neuedu.utils.GetConnectionUntils;

public class ProDaoImpl implements ProDao {

	@Override
	public List<Product> getAllProducts() {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="select * from pro";
		List<Product> lists = new ArrayList<>();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement = OneConnection.prepareStatement(sql);
			rs = prepareStatement.executeQuery();
			while(rs.next()) {
				Integer proID = rs.getInt(1);
				String proNum=rs.getString(2);
				String proName=rs.getString(3);
				double proPrice=rs.getDouble(4);
				Integer proCount=rs.getInt(5);
				String proMannufacturer=rs.getString(6);
				Date proDate=rs.getDate(7);
				String proImg=rs.getString(8);
				String proDes=rs.getString(9);
				String proPlace=rs.getString(10);
				Integer cId =rs.getInt(11);
				Product product = new Product(proID, proNum,  proName, proPrice,  proCount,
						proMannufacturer,  proDate,proImg, proDes, proPlace,  cId);
				lists.add(product);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection,prepareStatement, rs);
		}
		return lists;
	}
	/*public static void main(String[] args) {
		 List<Product> allProducts= new ProDaoImpl().getAllProducts();
		 System.out.println(allProducts);
	}*/
	@Override
	public int getAllCounts() {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="select count(*) from pro";
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement = OneConnection.prepareStatement(sql);
			rs =prepareStatement.executeQuery();
			if(rs.next())
			{
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection,prepareStatement, rs);
		}
		return 0;
	}
	@Override
	public List<Product> getProsByLimit(int pageNum, int pageSize) {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="select * from pro limit ?,?";
		List<Product> lists= new ArrayList<>();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		try {
			prepareStatement = OneConnection.prepareStatement(sql);
			prepareStatement.setInt(1, pageNum);
			prepareStatement.setInt(2, pageSize);
			rs = prepareStatement.executeQuery();
			while(rs.next())
			{
				Integer proID = rs.getInt(1);
				String proNum=rs.getString(2);
				String proName=rs.getString(3);
				double proPrice=rs.getDouble(4);
				Integer proCount=rs.getInt(5);
				String proMannufacturer=rs.getString(6);
				Date proDate=rs.getDate(7);
				String proImg=rs.getString(8);
				String proDes=rs.getString(9);
				String proPlace=rs.getString(10);
				Integer cId =rs.getInt(11);
				Product product = new Product(proID, proNum,  proName, proPrice,  proCount,
						proMannufacturer,  proDate,proImg, proDes, proPlace,  cId);
				lists.add(product);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection,prepareStatement, rs);
		}
		return lists;
	}
	@Override
	public boolean updateOneProduct(Product pro) {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="update pro set pro_Name =?,pro_price=?,pro_count=?,pro_manufacturer=?,pro_date=?,pro_img=?,pro_des=?,pro_place=?,cid=? where pro_id=?";
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = OneConnection.prepareStatement(sql);
			prepareStatement.setString(1, pro.getProName());
			prepareStatement.setDouble(2, pro.getProPrice());
			prepareStatement.setInt(3, pro.getProCount());
			prepareStatement.setString(4, pro.getProMannufacturer());
			prepareStatement.setDate(5, pro.getProDate());
			prepareStatement.setString(6, pro.getProImg());
			prepareStatement.setString(7, pro.getProDes());
			prepareStatement.setString(8, pro.getProPlace());
			prepareStatement.setInt(9, pro.getcId());
			prepareStatement.setInt(10, pro.getProID());
			int update = prepareStatement.executeUpdate();
			if(update!=0)
			{
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection,prepareStatement, null);
		}
		return false;
	}
	@Override
	public Product queryProsByUp(int proID) {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="select * from pro where pro_id=?";
		PreparedStatement prepareStatement = null;
		ResultSet rs =null;
		try {
			prepareStatement = OneConnection.prepareStatement(sql);
			prepareStatement.setInt(1, proID);
			rs = prepareStatement.executeQuery();
			while(rs.next())
			{Integer prId = rs.getInt(1);
			String proNum=rs.getString(2);
			String proName=rs.getString(3);
			double proPrice=rs.getDouble(4);
			Integer proCount=rs.getInt(5);
			String proMannufacturer=rs.getString(6);
			Date proDate=rs.getDate(7);
			String proImg=rs.getString(8);
			String proDes=rs.getString(9);
			String proPlace=rs.getString(10);
			Integer cId =rs.getInt(11);
			Product product = new Product(prId, proNum,  proName, proPrice,  proCount,
					proMannufacturer,  proDate,proImg, proDes, proPlace,  cId);
			return product;	
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			CloseUtils.close(OneConnection,prepareStatement, rs);
		}
		return null;
	}
	@Override
	public void delOneProduct(int proID) {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="DELETE FROM pro where pro_id=?";
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = OneConnection.prepareStatement(sql);
			prepareStatement.setInt(1, proID);
			int executeUpdate=prepareStatement.executeUpdate();
			System.out.println("删除了"+executeUpdate+"行");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection,prepareStatement, null);
		}
	}
	@Override
	public void addOnePro(Product pro) {
		Connection OneConnection = GetConnectionUntils.getOneConnection();
		String sql ="insert into pro(pro_Num,pro_Name,pro_price,pro_count,pro_manufacturer,pro_date,pro_img,pro_des,pro_place,cid) values(?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement=OneConnection.prepareStatement(sql);
			prepareStatement.setString(1,pro.getProNum());
			prepareStatement.setString(2, pro.getProName());
			prepareStatement.setDouble(3, pro.getProPrice());
			prepareStatement.setInt(4, pro.getProCount());
			prepareStatement.setString(5, pro.getProMannufacturer());
			prepareStatement.setDate(6, pro.getProDate());
			prepareStatement.setString(7, pro.getProImg());
			prepareStatement.setString(8, pro.getProDes());
			prepareStatement.setString(9, pro.getProPlace());
			prepareStatement.setInt(10, pro.getcId());
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			CloseUtils.close(OneConnection, prepareStatement, null);
		}
	}
}
